from django.http import FileResponse
from django.shortcuts import render,redirect
from front import models
from django.shortcuts import HttpResponse
import json
from django.views.decorators.csrf import csrf_exempt
import sqlite3
import xlrd
from utils import restful
import datetime,time
import time
import os
import json
from shutil import copyfile
'''from apscheduler.scheduler import Scheduler'''
global currentId

import geopandas as gp
import matplotlib.pyplot as plt
from shapely.geometry import Point
import requests
import matplotlib.image as mpimg
import pandas as pd
import numpy as np


def index(request):
    visualize_map()
    return render(request, "index.html")

def statistic(address_list):
    dict = {}
    province_list = ['上海市','云南省','内蒙古自治区' ,'北京市' ,'台湾省' ,'吉林省' ,
                     '四川省' ,'天津市' ,'宁夏回族自治区' ,'安徽省' ,'山东省' , '山西省' ,
                     '广东省' ,'广西壮族自治区' ,'新疆维吾尔自治区' ,'江苏省' ,'江西省' ,'河北省' ,
                     '河南省' ,'浙江省' ,'海南省' ,'湖北省' ,'湖南省' ,'澳门特别行政区' ,'甘肃省' ,
                     '福建省' ,'西藏自治区' ,'贵州省' ,'辽宁省' ,'重庆市' ,'陕西省' ,'青海省' ,
                     '香港特别行政区' ,'黑龙江省' ]
    count = np.zeros((1,34))[0]
    for address in address_list:
        for i in range(len(province_list)):
            if province_list[i] in address:
                count[i] = count[i] + 1
    for i in range(len(count)):
        dict[province_list[i]] = count[i]
    dict = sorted(dict.items(), key=lambda d: d[1], reverse=True)
    dict = dict[0:5]
    top_five_index = []
    for i in dict:
        top_five_index.append(province_list.index(i[0]))

    return count, dict, top_five_index

def calculate_height(list):
    dict = sorted(list.items(), key=lambda d: d[1], reverse=True)
    print(type(dict), dict)
    tmp_height = []
    for i in range(5):
        tmp_height.append(dict[i][1])

    for i in range(4):
        if (tmp_height[i] -tmp_height[i+1]) < 300000:
            print('差：',(tmp_height[i] -tmp_height[i+1]))
            tmp_height[i + 1] = tmp_height[i + 1]- (300000 - (tmp_height[i] -tmp_height[i + 1])) - 50000
    height_dict = {}
    print(tmp_height)
    for i in range(5):
        height_dict[dict[i][0]] = tmp_height[i]
    return height_dict



def visualize_map():
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    request_data = []
    lng = []
    lat = []

    sql_word = ["select Location from front_trafficdisaster", "select Location from front_waterdisaster",
            "select Location from front_oildisaster", "select Location from front_gasdisaster",
            "select Location from front_powerdisaster", "select Location from front_trafficdisaster",
            "select Location from front_irrigationdisaster"]

    for sql in sql_word:
        cur.execute(sql)
        tmp_list = cur.fetchall()
        for tmp in tmp_list:
            for i in tmp:
                request_data.append(i)
    print(request_data)

    plt.rcParams["font.family"] = "SimHei"  # 设置全局中文字体为黑体
    albers_proj = '+proj=aea +lat_1=25 +lat_2=47 +lon_0=105'
    # 读入中国领土面数据
    china = gp.read_file('D:/Geo/china-shapefiles/china.shp', encoding='utf-8')
    # 由于每行数据是单独的面，因此按照其省份列OWNER融合
    china = china.dissolve(by='OWNER').reset_index(drop=False)
    # 读入南海九段线线数据
    nine_lines = gp.read_file('D:/Geo/china-shapefiles/china_nine_dotted_line.shp', encoding='utf-8')

    # 背景图片
    fig = plt.figure(figsize=(16, 9))
    img = mpimg.imread('bg.jpg')
    axes = fig.add_axes([0, 0, 1, 1])
    axes.imshow(img)
    axes.axis('off')


    # 创建覆盖整个画布的子图1
    ax = fig.add_axes((0.15, 0.1, 0.75, 0.75))

    df = pd.DataFrame(china['OWNER'])
    count, top_five, top_five_index = statistic(request_data)
    df['count'] = gp.GeoSeries(count)    # 灾情统计
    data_with_geometry = pd.merge(left=china,
                                  right=df,
                                  left_on='OWNER',
                                  right_on='OWNER',
                                  how='right'
                                  ).loc[:, ['OWNER',
                                            'count',
                                            'geometry'
                                            ]]
    # 将数据从DataFrame转换为GeoDataFrame
    data_with_geometry = gp.GeoDataFrame(data_with_geometry, crs='EPSG:4326')
    bound = gp.GeoDataFrame({
        'x': [80, 150, 106.5, 123],
        'y': [15, 50, 2.8, 24.5]
    })
    # 添加矢量列
    bound.geometry = bound.apply(lambda row: Point([row['x'], row['y']]), axis=1)
    # 初始化CRS
    bound.crs = 'EPSG:4326'
    # 再投影
    bound.to_crs(albers_proj, inplace=True)
    print("bound: " , bound)
    ax.set_xlim(bound.geometry[0].x, bound.geometry[1].x)
    ax.set_ylim(bound.geometry[0].y, bound.geometry[1].y)
    ax = data_with_geometry.to_crs(albers_proj).plot(ax=ax,
                                                 column='count',
                                                 cmap='OrRd',
                                                 legend=True,
                                                 scheme='NaturalBreaks',
                                                 k=6,
                                                 legend_kwds={
                                                     'loc': 'lower left',
                                                     'title': '灾情等级',
                                                     'shadow': True
                                                 })

    ax = nine_lines.geometry.to_crs(albers_proj).plot(ax=ax, edgecolor='grey', linewidth=3, alpha=0.4)
    ax.axis('off')  # 移除坐标轴

    for data in request_data:
        url = 'http://api.map.baidu.com/geocoding/v3/?address=' + str(data) + '&output=json&ak=TD4f1SGEmpoqqn3NAx7KsnCvyO625ALm'
        res = requests.get(url).json()
        lng.append(res['result']['location']['lng'])
        lat.append(res['result']['location']['lat'])

    request_list = gp.GeoDataFrame({
        'x': lng,
        'y': lat
    })
    # 添加矢量列
    request_list.geometry = request_list.apply(lambda row: Point([row['x'], row['y']]), axis=1)
    # 初始化CRS
    request_list.crs = 'EPSG:4326'
    # 再投影
    request_list.to_crs(albers_proj, inplace=True)
    request_list.geometry.plot(ax=ax, marker='o', color='#1E90FF', markersize=5)
    print(request_list)

    pts = china.geometry.representative_point() .to_crs(albers_proj)

    # 给图添加注释
    annotate1 = top_five[0]
    annotate2 = top_five[1]
    annotate3 = top_five[2]
    annotate4 = top_five[3]
    annotate5 = top_five[4]

    height_list = {}
    for i in range(5):
        height_list[str(i)] = list(pts[top_five_index[i]].coords)[0][1]

    height_list = calculate_height(height_list)
    p1_x = [list(pts[top_five_index[0]].coords)[0][0], 2500000]
    p1_y = [list(pts[top_five_index[0]].coords)[0][1], height_list.get(str(0))]
    p2_x = [list(pts[top_five_index[1]].coords)[0][0], 2500000]
    p2_y = [list(pts[top_five_index[1]].coords)[0][1], height_list.get(str(1))]
    p3_x = [list(pts[top_five_index[2]].coords)[0][0], 2500000]
    p3_y = [list(pts[top_five_index[2]].coords)[0][1], height_list.get(str(2))]
    p4_x = [list(pts[top_five_index[3]].coords)[0][0], 2500000]
    p4_y = [list(pts[top_five_index[3]].coords)[0][1], height_list.get(str(3))]
    p5_x = [list(pts[top_five_index[4]].coords)[0][0], 2500000]
    p5_y = [list(pts[top_five_index[4]].coords)[0][1], height_list.get(str(4))]
    plt.plot(p1_x, p1_y, color='grey', linewidth=2.0, linestyle='--')
    plt.plot(p2_x, p2_y, color='grey', linewidth=2.0, linestyle='--')
    plt.plot(p3_x, p3_y, color='grey', linewidth=2.0, linestyle='--')
    plt.plot(p4_x, p4_y, color='grey', linewidth=2.0, linestyle='--')
    plt.plot(p5_x, p5_y, color='grey', linewidth=2.0, linestyle='--')

    plt.annotate(s=annotate1[0] + "\n灾情数量： "+ str(annotate1[1]),
                 xy=list(pts[top_five_index[0]].coords)[0],
                 xytext=(2500000, height_list.get(str(0))),
                 bbox=dict(boxstyle="round",
                           ec=(0.34,0.34,0.34),
                           fc=(0.75,0.75,0.75,0.9),
                           )
                 )
    plt.annotate(s=annotate2[0] + "\n灾情数量： " + str(annotate2[1]),
                 xy=list(pts[top_five_index[1]].coords)[0],
                 xytext=(2500000, height_list.get(str(1))),
                 bbox=dict(boxstyle="round",
                           ec=(0.34, 0.34, 0.34),
                           fc=(0.75, 0.75, 0.75, 0.9),
                           )
                 )
    plt.annotate(s=annotate3[0] + "\n灾情数量： " + str(annotate3[1]),
                 xy=list(pts[top_five_index[2]].coords)[0],
                 xytext=(2500000, height_list.get(str(2))),
                 bbox=dict(boxstyle="round",
                           ec=(0.34, 0.34, 0.34),
                           fc=(0.75, 0.75, 0.75, 0.9),
                           )
                 )
    plt.annotate(s=annotate4[0] + "\n灾情数量： " + str(annotate4[1]),
                 xy=list(pts[top_five_index[3]].coords)[0],
                 xytext=(2500000, height_list.get(str(3))),
                 bbox=dict(boxstyle="round",
                           ec=(0.34, 0.34, 0.34),
                           fc=(0.75, 0.75, 0.75, 0.9),
                           )
                 )
    plt.annotate(s=annotate5[0] + "\n灾情数量： " + str(annotate5[1]),
                 xy=list(pts[top_five_index[4]].coords)[0],
                 xytext=(2500000, height_list.get(str(4))),
                 bbox=dict(boxstyle="round",
                           ec=(0.34, 0.34, 0.34),
                           fc=(0.75, 0.75, 0.75, 0.9),
                           )
                 )
    plt.draw()
    plt.show()
    fig.savefig('static/res/background.png', dpi=300)
    cur.close()
    conn.close()



def upload(request):
    return render(request,"upload.html")


def link_status(request):
    return render(request,"status.html")


def apply(request):
    return render(request,"apply.html")


@csrf_exempt
def upload_file(request):
    uploadFileObj = request.FILES['myFile']
    uploadFileName = "dataResource/" + uploadFileObj.name
    MScode = request.POST.get("types")
    with open(uploadFileName ,'wb') as saveFile:
        for chunk in uploadFileObj.chunks():
            saveFile.write(chunk)
    return restful.result(message="upload success", data={"MScode": MScode, "uploadFileName": uploadFileObj.name})


# 初始化sched模块的scheduler类
# 第一个参数是一个可以返回时间戳的函数，第二个参数可以在定时未到达之前阻塞。
'''sched = Scheduler()  # 实例化，固定格式
@sched.interval_schedule(seconds=5)  # 装饰器，seconds=60意思为该函数为1分钟运行一次
def mytask():
    execute_command()
sched.start()  # 启动该脚本'''

# 被周期性调度触发的函数
def execute_command():
    print('每10分钟执行一次')
    backupsql()    # 数据库备份
    rootdir = 'dataResource'
    list = os.listdir(rootdir)
    if len(list)==0:
        print('不存在文件')
    else:
        print('存在文件')
        # 列出文件夹下所有的目录与文件
        for i in range(0, len(list)):
            path = os.path.join(rootdir, list[i])
            if os.path.isfile(path):
                f = open(path, encoding='utf-8')
                data_json = json.load(f)
                code = 0
                sequenceNum = []
                data = xlrd.open_workbook('shandongdata.xlsx', encoding_override='utf-8')  # 读取表
                for dataj in data_json:
                    DisasterType, sqNumber = TransData(dataj["Code"])  # 解码ID，返回后七位的灾情信息码中的大类代码和子类代码
                    code = DisasterType
                    sequenceNum.append(sqNumber)
                    dataj["ReportingUnit"] = "202-" + dataj["ReportingUnit"]  # 将编码MSCode加在reportingunit字段中
                    disInfo = dataj["Code"]  # 获取19位编码
                    dataj["Location"] = TransDataAddress(data, disInfo[
                                                               0:12])  #  获取19位ID的前十二位编码，即基础地理信息码  # 获取19位ID的前十二位编码，即基础地理信息码
                    print("DisasterType:" + DisasterType)
                    print("data:" + str(dataj))
                databaseOperation(code, data_json, sequenceNum)
                f.close()
                os.remove(path)
                print('ok')


def backupsql():
    BACK_DIR = 'backupsql'
    # 如果目录不存在，新建目录
    if not os.path.exists(BACK_DIR):
        os.makedirs(BACK_DIR)
    conn = sqlite3.connect("db.sqlite3")
    # 备份中保留三个最新时间的数据，超时半小时的旧数据淘汰
    #db1.sql.bak对应最新的数据
    BACK_DIR1 =BACK_DIR + '/db1.sql.bak'
    BACK_DIR2 = BACK_DIR + '/db2.sql.bak'
    BACK_DIR3 = BACK_DIR + '/db3.sql.bak'

    if not os.path.exists(BACK_DIR1):
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    elif not os.path.exists(BACK_DIR2):
        #copyfile(source_file, destination_file)
        with open(BACK_DIR2, "wb+") as f:
            copyfile(BACK_DIR1,BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    elif not os.path.exists(BACK_DIR3):
        with open(BACK_DIR3, "wb+") as f:
            copyfile(BACK_DIR2,BACK_DIR3)
        copyfile(BACK_DIR1, BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    else:
        copyfile(BACK_DIR2, BACK_DIR3)
        copyfile(BACK_DIR1, BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)

# 解析19位灾情信息编码
def TransData(IDNUmber):
    if(CheckIDNumber(IDNUmber)):
        disInfo=IDNUmber[-7:] #获取灾情信息编码，从倒数第七个字符到结尾
        sequenceNumber = disInfo[-4:]
        fdisInfo=disInfo[0] #获取大类代码
        sdisInfo=disInfo[1:3] #获取子类代码
        print('灾情信息编码：'+disInfo+' 大类代码：'+fdisInfo+' 子类代码：'+sdisInfo)
        if(fdisInfo=='1'and sdisInfo=='11'):
            dbTableNmae="DeathStatistics"  # 人员失踪及死亡：死亡
            dbTableType=fdisInfo+sdisInfo
        elif(fdisInfo=='2'and sdisInfo=='21'):
            dbTableNmae="CivilStructure"  # 房屋破坏：土木
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='3'and sdisInfo=='36'):
            dbTableNmae="CommDisaster"  # 生命线工程灾情：通信
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='4'and sdisInfo=='41'):
            dbTableNmae="CollapseRecord"    # 次生灾害：崩塌
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='5'and sdisInfo=='52'):
            dbTableNmae="DisasterPrediction"  # 震情：灾情预测
            dbTableType = fdisInfo + sdisInfo
        else:
            dbTableNmae="000"
            dbTableType=fdisInfo+sdisInfo
        print(dbTableNmae+dbTableType)
        return dbTableType,sequenceNumber# 返回灾情信息编码的大类代码+子类代码


# 解析19位ID编码的前12位，得到具体的地理位置信息
def TransDataAddress(data,address_code):
    table = data.sheets()[0]  # 选定表
    nrows = table.nrows  # 获取行号
    # print('共有'+str(nrows)+'行数据')
    address_info = '地址'
    for i in range(1, nrows):  # 第0行为表头
        alldata = table.row_values(i)  # 循环输出excel表中每一行，即所有数据
        if (alldata[9] == address_code):
            # print(alldata[9])
            result = alldata[10] + alldata[11] + alldata[12] + alldata[13] + alldata[14]
            # print('查到的地址为：'+result)
            address_info = result
            # print('address_info:'+address_info)
            break
        else:
            address_info = '未找到'

    return address_info


@csrf_exempt
def test_url(request):
    print("test_url")
    result = request.POST.get("data1")
    return HttpResponse(result)


#数据表操作和文件的存储
def databaseOperation(code, datas,sequenceNumber):
    # 找到解析的336编码，不同编码不同目录
    if (code == '336'):
        fileName = '336/CommDisaster.json'
    if (code == '111'):
        fileName = '111/DeathStatistics.json'
    if (code == '221'):
        fileName = '221/CivilStructure.json'
    if (code == '441'):
        fileName = '441/CollapseRecord.json'
    if (code == '552'):
        fileName = '552/DisasterPrediction.json'

    # 为了方便操作，每次运行，，我都先清空数据表了
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    table_name = "front_commdisaster"
    # sql = "Delete From {0}".format(table_name)
    # cur.execute(sql)
    # cur.execute("UPDATE sqlite_sequence set seq=0 where name='MSHD_commdisaster';")
    # conn.commit()
    # 将数据存入数据库
    i=0
    for data in datas:
        models.CommDisaster.objects.create(Code=data["Code"], Date=data["Date"], Location=data["Location"],
                                           Type=data["Type"], Grade=data["Grade"], Picture=data["Picture"],
                                           Note=data["Note"], ReportingUnit=data["ReportingUnit"],
                                           SequenceNumber=sequenceNumber[i])
        i=i+1
    # 将字典封装成json文件
    json_file = json.dumps(datas)
    # 保存json文件
    with open(fileName, 'w') as file_obj:
        json.dump(json_file, file_obj)
    return datas


# 测试数据是否是json格式，如果是json格式则抛出异常
def test_is_json(myjson):
    try:
        json.loads(myjson)
    except ValueError:
        return False
    return True


# 测试数据源的ID是否为19位编码
def test_source_data_ID_number(ID_number):
    if(len(ID_number)==19):
        return True
    else:
        return False


# 检查ID是否为19位编码
def CheckIDNumber(IDNumber):
    if(len(IDNumber)==19):
        return True
    else:
        return False

tableMap = {"331":"front_trafficdisaster",
            "332":"front_waterdisaster",
            "333":"front_oildisaster",
            "334":"front_gasdisaster",
            "335": "front_powerdisaster",
            "336": "front_commdisaster",
            "337": "front_irrigationdisaster"
          }
#"+str(tableMap[table_name])+"
#global table_name
table_name="111111"
#将json文件传到前段html文件中
def data(request):
    if request.method == "GET":
        list = request.path_info.split('/')
        global table_name
        #print(list)
        if list[2]=="nothing happened":
            return
        table_name=list[2]
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])  # MSHD_commdisaster为表名
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        cur.close()
        conn.close()
        input_type="button"
        submit_value="修改"
        BACKGROUND_COLOR="BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type': list[2],'input_type':input_type,
                       'submit_value':submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas, 'type': list[2]})
    if request.method == "POST" and request.POST.get("submit") == "删除":
        id = request.POST.get("select")
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "delete  from "+str(tableMap[table_name])+" where id=" + id  # MSHD_commdisaster为表名
        cur.execute(sql)
        sql2 = "select * from "+str(tableMap[table_name])
        cur.execute(sql2)
        datas = cur.fetchall()  # 搜取所有结果
        conn.commit()
        cur.close()
        conn.close()
        input_type = "button"
        submit_value = "修改"
        BACKGROUND_COLOR = "BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type': table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas,'type': table_name,'input_type':input_type})
        #return render(request, 'data.html', {'items': datas,'type': table_name})
    if request.method == "POST" and request.POST.get("submit") == "修改":
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])  # MSHD_commdisaster为表名
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        cur.close()
        conn.close()
        input_type = "text"
        submit_value = "提交修改"
        BACKGROUND_COLOR=""
        return render(request, 'data.html',
                      {'items': datas, 'type': table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
    if request.method == "POST" and request.POST.get("submit") == "提交修改":
        #location = request.POST.get("23")
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        for data in datas:
            location = "\"" + request.POST.get("fixLocation"+str(data[0])) + "\""
            grade = request.POST.get("fixGrade"+str(data[0]))
            date=request.POST.get("fixDate"+str(data[0]))
            sql1 = "UPDATE "+str(tableMap[table_name])+" SET Location=" + str(location) + ",Grade=" + str(
                grade) +",Date=" + str(date) + " WHERE id = " + str(data[0])
            #print(sql1)
            cur.execute(sql1)
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        conn.commit()
        cur.close()
        conn.close()
        input_type = "button"
        submit_value = "修改"
        BACKGROUND_COLOR = "BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type':table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas, 'type': table_name, 'input_type': input_type})
        #return render(request, 'data.html', {'items': datas,'type': table_name})


# 用户访问 /request 网页并传入 code disasterType  o_URL requestunit
# ex: http://localhost:8000/request/?code=3700000000003366789&disasterType=336&o_url=168.336.222&requestUnit=水星水星
def data_request(request):
    # 获取问号后传递的参数
    location = request.POST.get("location")
    disaster_type = request.POST.get("disasterType")
    o_url = request.POST.get("o_url")
    request_unit = request.POST.get("requestUnit")
    type = request.POST.get("type")
    # 获取当前时间，以年-月-日-时-分格式显示
    now_time = datetime.datetime.now()
    str_time = now_time.strftime("%Y-%m-%d %X")
    tup_time = time.strptime(str_time, "%Y-%m-%d %X")
    time_str = str(tup_time.tm_year) + "-" + str(tup_time.tm_mon) +"-"+str(tup_time.tm_mday)+" "+ str(tup_time.tm_hour) + ":" +str(tup_time.tm_min)
    # 将记录写入数据库
    models.requestList.objects.create(location=location, disasterType=disaster_type,
                                      status=0, o_URL=o_url, requestunit=request_unit,
                                      date=time_str, type=type)

    return restful.result(message="request success")


# 选择查看已发送还是未发送
def status(request):
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    sql_word = "select * from front_requestList where status = 1 "  # 显示全部数据

    cur.execute(sql_word)
    request_data = cur.fetchall()  # 搜取所有结果
    cur.close()
    conn.close()

    # 进行筛选，已发送数据、未发送数据、发送数据
    if request.POST:
        flag = 0
        status = request.POST.get('status',None)
        sendId = request.POST.get('send',None)

        if (status == "已处理"):   # 已发送数据
            sql_word = "select * from front_requestList where status = 1 "
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word)
            request_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            response = HttpResponse(json.dumps(request_data))
            flag = 0

        if (status == "未处理"):  # 未发送数据
            sql_word = "select * from front_requestList where status = 0 "
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word)
            request_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            response = HttpResponse(json.dumps(request_data))
            flag=1

        if(sendId != None):  #发送数据，更改status的值
            sql_word1 = "update front_requestList set status = 1 where id= {0} ".format(sendId)  # 更新数据
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word1)
            conn.commit()
            sql_word2 = "select * from front_requestList where status = 0 "  # 显示剩余的status = 0的数据
            cur.execute(sql_word2)
            request_data = cur.fetchall()  # 搜取所有结果
            flag = 1
            # 向指定url发送数据
            # 获得记录的url以及disasterType
            sql_word="select * from front_requestList where id={0}".format(sendId)
            cur.execute(sql_word)
            statusData = cur.fetchall()
            o_url=statusData[0][5]
            disaster_type=statusData[0][3]
            code=statusData[0][1]
            type = statusData[0][7]


            table_name=''
            # 获取要发送的数据
            if (disaster_type == '336'):
                table_name = "front_commdisaster"
            if (disaster_type == '111'):
                print("暂时没有这个表")
            if (disaster_type == '221'):
                print("暂时没有这个表")
            if (disaster_type == '441'):
                print("暂时没有这个表")
            if (disaster_type == '552'):
                print("暂时没有这个表")

            sql = "select * from {} where code = {}".format(table_name,code)
            print(sql)
            cur.execute(sql)
            send_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            # 将获取到的数据转换为json格式
            if len(send_data)==0:
                return restful.result(message="查询失败，数据库无此数据！")
            print(send_data)
            if(type == "all"):
               send_dic = {
                'code':send_data[0][1],'date':send_data[0][2],
                'location': send_data[0][3], 'type': send_data[0][4],
                'sequenceNumber': send_data[0][5], 'grade': send_data[0][6],
                'picture': send_data[0][7], 'note': send_data[0][8],
                'reportingUnit': send_data[0][9]
            }
            if(type=="code"):
                send_dic={
                'code': send_data[0][1]
            }
            send_data = json.dumps(send_dic,ensure_ascii=False,indent=4)
            # 创建url专属文件夹
            curPath = os.getcwd()
            targetPath = curPath+os.path.sep+'sendResource'+os.path.sep+str(o_url)
            print(targetPath)
            if not os.path.exists(targetPath):
                os.makedirs(targetPath)
            file_name="{}.json".format(table_name)
            filePath=targetPath+os.path.sep+file_name
            with open(filePath, 'w') as f:
                f.write(send_data)
            f.close()
            print("写入成功")

    return render(request,'status.html', {'items': request_data,'flag':flag})

# 下载使用说明书
def download_instructions(request):
    file = open('static/file/instructions.pdf', 'rb')
    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename="instructions.pdf"'
    return response


def login(request):
    if request.session.get('is_login', None):
        return redirect('/index')

    if request.method == "POST":
        login_form = models.UserForm(request.POST)
        message = "请检查填写的内容！"
        if login_form.is_valid():
            username = login_form.cleaned_data['username']
            password = login_form.cleaned_data['password']
            try:
                user = models.User.objects.get(name=username)
                if user.password == password:
                    request.session['is_login'] = True
                    request.session['user_id'] = user.id
                    request.session['user_name'] = user.name
                    request.session['user_role'] = user.role
                    return redirect('/index')
                else:
                    message = "密码不正确！"
            except:
                message = "用户不存在！"
        return render(request, 'login.html', locals())

    login_form = models.UserForm()
    return render(request, 'login.html', locals())


def register(request):
    if request.session.get('is_login', None):
        # 登录状态不允许注册。你可以修改这条原则！
        return redirect("/index/")
    if request.method == "POST":
        register_form = models.RegisterForm(request.POST)
        message = "请检查填写的内容！"
        if register_form.is_valid():  # 获取数据
            username = register_form.cleaned_data['username']
            role=register_form.cleaned_data['role']
            password1 = register_form.cleaned_data['password1']
            password2 = register_form.cleaned_data['password2']
            if password1 != password2:  # 判断两次密码是否相同
                message = "两次输入的密码不同！"
                return render(request, 'register.html', locals())
            else:
                same_name_user = models.User.objects.filter(name=username)
                if same_name_user:  # 用户名唯一
                    message = '用户已经存在，请重新选择用户名！'
                    return render(request, 'register.html', locals())

                # 当一切都OK的情况下，创建新用户

                new_user = models.User.objects.create()
                new_user.name = username
                new_user.password = password1
                new_user.role=role
                new_user.save()
                return redirect('/login/')  # 自动跳转到登录页面
    register_form = models.RegisterForm()
    return render(request, 'register.html', locals())

def logout(request):
    if not request.session.get('is_login', None):
        # 如果本来就未登录，也就没有登出一说
        return redirect("/")
    request.session.flush()

    return redirect("/")